﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using DATN.Entities;
using DATN.Common;


namespace DATN.DataAccess
{
    public class LichCongTacDataAccess
    {
        public static Lichcongtac ParseToEntity(IDataRecord dataRecord)
        {
            return new Lichcongtac
            {
                Ma_ID = dataRecord["Ma_ID"].ToNumeric(),
                Tieu_de = dataRecord["Tieu_de"].ToStringOrDefault(),
                Noi_dung = dataRecord["Noi_dung"].ToStringOrDefault(),
                Ma_loai = dataRecord["Ma_loai"].ToStringOrDefault(),
                Ma_nhom = dataRecord["Ma_nhom"].ToStringOrDefault(),
                Hinh_thuc = dataRecord["Hinh_thuc"].ToStringOrDefault(),
                Muc_do_quan_tam = dataRecord["Muc_do_quan_tam"].ToStringOrDefault(),
                Link_tai_lieu_lien_quan = dataRecord["Link_tai_lieu_lien_quan"].ToStringOrDefault(),
                Ma_dia_diem = dataRecord["Ma_dia_diem"].ToStringOrDefault(),
                Dia_diem = dataRecord["Dia_diem"].ToStringOrDefault(),
                Ngay_bat_dau_kh = Convert.ToDateTime(dataRecord["Ngay_bat_dau_kh"]),
                Ngay_ket_thuc_kh = Convert.ToDateTime(dataRecord["Ngay_ket_thuc_kh"]),
                //Ngay_bat_dau_td = Convert.ToDateTime(dataRecord["Ngay_bat_dau_td"]),
                //Ngay_ket_thuc_td = Convert.ToDateTime(dataRecord["Ngay_ket_thuc_td"]),
                Ly_do_td = dataRecord["Ly_do_td"].ToStringOrDefault(),
                //Ngay_bat_dau_tt = Convert.ToDateTime(dataRecord["Ngay_bat_dau_tt"]),
                //Ngay_ket_thuc_tt = Convert.ToDateTime(dataRecord["Ngay_ket_thuc_tt"]),
                //Ngay_huy_kh = Convert.ToDateTime(dataRecord["Ngay_huy_kh"]),
                Ly_do_huy_kh = dataRecord["Ly_do_huy_kh"].ToStringOrDefault(),
                Ma_nv_chu_tri = dataRecord["Ma_nv_chu_tri"].ToStringOrDefault(),
                Nguoi_chu_tri = dataRecord["Nguoi_chu_tri"].ToStringOrDefault(),
                Nguoi_thay_the = dataRecord["Nguoi_thay_the"].ToStringOrDefault(),
                Ly_do_thay_the = dataRecord["Ly_do_thay_the"].ToStringOrDefault(),
                Nhan_xet_ket_qua = dataRecord["Nhan_xet_ket_qua"].ToStringOrDefault(),
                Phan_loai_ket_qua = dataRecord["Phan_loai_ket_qua"].ToStringOrDefault(),
                Danh_gia_ket_qua = dataRecord["Danh_gia_ket_qua"].ToStringOrDefault(),
                Ma_nv_danh_gia = dataRecord["Ma_nv_danh_gia"].ToStringOrDefault(),
                //Ngay_danh_gia = Convert.ToDateTime(dataRecord["Ngay_danh_gia"]),
                Trang_thai = dataRecord["Trang_thai"].ToStringOrDefault(),
                Thanh_phan = dataRecord["Thanh_phan"].ToStringOrDefault(),
                Ghi_chu = dataRecord["Ghi_chu"].ToStringOrDefault(),
                //Kt_trung_gio_chu_tri = Convert.ToBoolean(dataRecord["Kt_trung_gio_chu_tri"]),
                //Kt_trung_gio_tham_gia = Convert.ToBoolean(dataRecord["Kt_trung_gio_tham_gia"]),
                Gio_bat_dau = dataRecord["Gio_bat_dau"].ToNumeric()
            };
        }

        /// <summary>
        /// lay toan bo lich cong tac theo ngay
        /// </summary>
        /// <param name="dateLichCongviec"></param>
        /// <returns></returns>
        public List<Lichcongtac> GetAllLichCongTacByDate(string dateLichCongviec)
        {
            string truyVan = "SELECT * FROM Lichcongtac Where Ngay_bat_dau_kh ='" + getDate(DateTime.Parse(dateLichCongviec)) + "' ORDER BY Gio_bat_dau";
            SqlCommon sql = new SqlCommon();
            IDataReader reader = sql.ExecuteReader(truyVan);
            if (reader == null) return null;
            List<Lichcongtac> listLct = new List<Lichcongtac>();
            while (reader.Read())
            {
                Lichcongtac lct = ParseToEntity(reader);
                listLct.Add(lct);
            }

            return listLct;
        }

        /// <summary>
        /// lay toan bo lich cong tac theo ngay va ten nguoi dang nhap
        /// </summary>
        /// <param name="dateLichCongviec"></param>
        /// <param name="thanhPhan"></param>
        /// <returns></returns>
        public List<Lichcongtac> GetAllLichCongTacByDateVaNguoiDangNhap(string dateLichCongviec, string thanhPhan)
        {
            string truyVan = "SELECT * FROM Lichcongtac Where ([Nguoi_chu_tri] = '" + thanhPhan + "' or [Nguoi_thay_the] = '" + thanhPhan + "') and Ngay_bat_dau_kh ='" + dateLichCongviec + "' ORDER BY Gio_bat_dau";
            SqlCommon sql = new SqlCommon();
            IDataReader reader = sql.ExecuteReader(truyVan);
            if (reader == null) return null;
            List<Lichcongtac> listLct = new List<Lichcongtac>();
            while (reader.Read())
            {
                Lichcongtac lct = ParseToEntity(reader);
                listLct.Add(lct);
            }

            return listLct;
        }
        private SqlConnection conn;
        private SqlCommand cmd;

        public Lichcongtac GetLichCongTacByID(int id)
        {
            string truyVan = "SELECT * FROM Lichcongtac Where Ma_ID=" + id + "";
            SqlCommon sql = new SqlCommon();
            IDataReader reader = sql.ExecuteReader(truyVan);
            if (reader == null) return null;
            if (reader.Read())
            {
                return ParseToEntity(reader);
            }
            return null;
        }

        //Thêm Lịch Công tác
        public bool InsertLich(Lichcongtac lichcongtac)
        {
            conn = new SqlConnection(AppConfig.ConnectionString);
            string truyVan = "insert into Lichcongtac(Tieu_de,Noi_dung,Hinh_thuc,Gio_bat_dau,Ma_dia_diem,Trang_thai,Nguoi_chu_tri,Dia_diem,Ngay_bat_dau_kh,Ngay_ket_thuc_kh,Thanh_phan,Ghi_chu)"
                                + "values(N'" + lichcongtac.Tieu_de + "',N'" + lichcongtac.Noi_dung + "','" + lichcongtac.Hinh_thuc + "'," +
                             "" + lichcongtac.Gio + ",'" + lichcongtac.Ma_dia_diem + "','" + lichcongtac.Trang_thai + "'," +
                             "N'" + lichcongtac.Nguoi_chu_tri + "',N'" + lichcongtac.Dia_diem + "','" + getDate(lichcongtac.Ngay_bat_dau_kh) + "'," +
                             "'" + getDate(lichcongtac.Ngay_ket_thuc_kh) + "',N'" + lichcongtac.Thanh_phan + "',N'" + lichcongtac.Ghi_chu + "')";
            cmd = new SqlCommand(truyVan, conn);
            conn.Open();
            return cmd.ExecuteNonQuery() > 0;
        }

        //Định dạng thời gian cho SQL
        public string getDate(DateTime dateTime)
        {
            string result = "";
            result = result + dateTime.Month + "/" + dateTime.Day + "/" + dateTime.Year;
            //result = result + dateTime.Year + "-" + dateTime.Month + "-" + dateTime.Day;
            return result;
        }

        ////Convert từ data sang object
        //public static Lichcongtac ParseToEntity(IDataRecord dataRecord)
        //{
        //    return new Lichcongtac
        //    {
        //        Ma_ID = dataRecord["Ma_ID"].ToStringOrDefault().ToNumeric(),
        //        Tieu_de = dataRecord["Tieu_de"].ToStringOrDefault(),
        //        Noi_dung = dataRecord["Noi_dung"].ToStringOrDefault(),
        //    };
        //}

        public Collection<Lichcongtac> GetAllLich()
        {
            conn = new SqlConnection(AppConfig.ConnectionString);
            string truyvan = "Select * from Lichcongtac";
            cmd = new SqlCommand(truyvan, conn);
            conn.Open();

            using (IDataReader iDataReader = cmd.ExecuteReader())
            {
                Collection<Lichcongtac> collection = new Collection<Lichcongtac>();

                while (iDataReader.Read())
                {
                    collection.Add(ParseToEntity(iDataReader));
                }
                return collection;
            }
        }

        public bool UpdateLich(Lichcongtac lich)
        {
            conn = new SqlConnection(AppConfig.ConnectionString);
            string truyVan =
                "update Lichcongtac set Tieu_de = N'" + lich.Tieu_de + "'," +
                "Noi_dung =N'" + lich.Noi_dung + "'," +
                "Hinh_thuc =" + lich.Hinh_thuc + "," +
                "Gio_bat_dau=" + lich.Gio_bat_dau + "," +
                "Ma_dia_diem = " + lich.Ma_dia_diem + "," +
                "Trang_thai = " + lich.Trang_thai + "," +
                "Nguoi_chu_tri = N'" + lich.Nguoi_chu_tri + "'," +
                "Dia_diem=N'" + lich.Dia_diem + "'," +
                "Ngay_bat_dau_kh = '" + getDate(lich.Ngay_bat_dau_kh) + "'," +
                "Ngay_ket_thuc_kh = '" + getDate(lich.Ngay_ket_thuc_kh) + "'," +
                "Thanh_phan = N'" + lich.Thanh_phan + "'," +
                "Ghi_chu=N'" + lich.Ghi_chu + "' where Ma_ID=" + lich.Ma_ID + " ";
            cmd = new SqlCommand(truyVan, conn);
            conn.Open();
            return cmd.ExecuteNonQuery() > 0;
        }
    }
}
